﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
using PgBirdie.Core.Config;
using PgBirdie.Core.Error;
using PgBirdie.Core.Model;
using PgBirdie.Core.Model.Database.AccessControl;
using PgBirdie.Core.Model.Database.Data;
using PgBirdie.Core.Model.Database.Structure;
using PgBirdie.Core.Model.Database.Structure.Constraint;
using PgBirdie.Core.Service.DbObject;
using PgBirdie.Core.Service.Dump;
using PgBirdie.Core.Service.Infrastructure;

namespace PgBirdie.Core.Service.Dump.v9_x
{
    public class DbFromPgDumpService : IDbFromPgDumpService
    {
        private readonly IDbObjectFromPgDumpService _dbObject;
        private readonly ITextFormatService _text;
        private readonly IDbObjectValidator _validator;

        public DbFromPgDumpService(IDbObjectFromPgDumpService dbObject, ITextFormatService text, IDbObjectValidator validator)
        {
            _dbObject = dbObject;
            _text = text;
            _validator = validator;
        }

        public PgDatabase GetDbStructureFromPgDump(string text)
        {
            var totalLines = text.Split(Settings.SplitterFor.WindowsLineBreak, StringSplitOptions.None);

            var db = new PgDatabase() { OrderNo = 0 };
            db.Schemas.Add("public", new PgSchema() { ModelType = PgDbObjectType.Schema, Database = db, Name = "public", OrderNo = 1});

            var currentSearchPath = String.Empty; // a.k.a. schema name
            var orderNo = 2; // For db object dependencies.
            var totalCount = totalLines.Length;

            for (var i = 0; i < totalCount; i++)
            {
                var line = totalLines[i].Trim();

                // Filter out non-meaningful lines.
                if (String.IsNullOrEmpty(line) || line.Length <= 2 || line.StartsWith("--") || line.StartsWith("  ") || line.StartsWith("COMMENT"))
                    continue;

                var lineSegments = _text.RemoveCommaOrSemiColonFromTheEnd(line)
                    .Split(Settings.SplitterFor.Space, StringSplitOptions.RemoveEmptyEntries);

                // Filter out non-meaningful lines.
                if (lineSegments.Length <= 2)
                    continue;

                if (lineSegments[1].Equals("search_path"))
                {
                    currentSearchPath = GetCurrentSearchPath(lineSegments);
                    continue;
                }

                if (line.StartsWith("CREATE DATABASE"))
                {
                    //db = _dbObject.GetDatabase(line);
                    var segments = line.Split(Settings.SplitterFor.Space, StringSplitOptions.RemoveEmptyEntries);
                    db.Name = _text.RemoveDoubleQuotes(segments[2]);
                    //db.OrderNo = orderNo;
                    db.Owner = _dbObject.GetOwner(totalLines[i - 3], db);
                    orderNo++;
                    continue;
                }

                // Creates additional schemas.
                if (line.StartsWith("CREATE SCHEMA"))
                {
                    var schema = _dbObject.GetSchema(line);
                    schema.Database = db;
                    schema.OrderNo = orderNo;
                    schema.Owner = _dbObject.GetOwner(totalLines[i - 3], schema);
                    db.Schemas.Add(schema.Name, schema);

                    orderNo++;
                    continue;
                }

                if (line.StartsWith("CREATE TABLE") || line.StartsWith("CREATE UNLOGGED TABLE"))
                {
                    var schema = GetCurrentSchema(currentSearchPath, db.Schemas);
                    var table = _dbObject.GetTable(line);
                    table.Schema = schema;
                    table.OrderNo = orderNo;
                    table.Owner = _dbObject.GetOwner(totalLines[i - 3], table);

                    var subIndex = i + 1;
                    for (var i2 = subIndex; i < totalCount; i2++)
                    {
                        var colLine = totalLines[i2].Trim();

                        // Marks end of table definition.
                        if (colLine.StartsWith(")"))
                        {
                            i = i2 + 1; // Set the processing to continue after end of table definition.
                            break;
                        }

                        // When line starts with a double quote (") it refers to a column.
                        // CONSTRAINT check_user__name CHECK ((name ~ '^[^A-Z]+$'::text)),
                        if (colLine.StartsWith("CONSTRAINT"))
                        {
                            var match = Regex.Match(colLine, @"CONSTRAINT (?<name>.+) CHECK \(?\((?<columnName>.+) (?<operation>[<>~]{1,3}) (?<expression>(.+::text)|(.+))?\)");
                            var name = match.Groups["name"].Value;
                            var columnName = match.Groups["columnName"].Value;
                            var operation = match.Groups["operation"].Value;
                            var expression = match.Groups["expression"].Value;
                            var check = new PgCheck(name, table, ConstraintType.Check, columnName, operation, expression);
                            table.Checks.Add(name, check);
                        }
                        else
                        {
                            var column = _dbObject.GetColumn(colLine);
                            column.Table = table;
                            column.OrderNo = orderNo;
                            table.Columns.Add(column.Name, column);
                        }

                        orderNo++;
                    }

                    schema.Tables.Add(table.Name, table);

                    orderNo++;
                    continue;
                }

                if (line.StartsWith("CREATE SEQUENCE"))
                {
                    var schema = GetCurrentSchema(currentSearchPath, db.Schemas);
                    var sequence = new PgSequence();
                    sequence.ModelType = PgDbObjectType.Sequence;
                    sequence.Schema = schema;
                    sequence.Name = _dbObject.GetSequenceName(line);
                    sequence.StartWith = _dbObject.GetSequenceStartWith(totalLines[i + 1].Trim());
                    sequence.IncrementBy = _dbObject.GetSequenceIncrementBy(totalLines[i + 2].Trim());
                    sequence.MinValue = _dbObject.GetSequenceMinValue(totalLines[i + 3].Trim());
                    sequence.MaxValue = _dbObject.GetSequenceMaxValue(totalLines[i + 4].Trim());
                    sequence.Cache = _dbObject.GetSequenceCache(totalLines[i + 5].Trim());
                    sequence.OrderNo = orderNo;
                    sequence.Owner = _dbObject.GetOwner(totalLines[i - 3], sequence);

                    schema.Sequences.Add(sequence.Name, sequence);

                    orderNo++;
                    continue;
                }

                if (lineSegments.Length >= 9 && lineSegments[8].StartsWith("nextval"))
                {
                    var tableName = lineSegments[2].Replace("\"", String.Empty);
                    var columnName = lineSegments[5].Replace("\"", String.Empty);
                    var sequenceName = _dbObject.GetColumnSequenceName(lineSegments[lineSegments.Length - 1]);

                    var schema = GetCurrentSchema(currentSearchPath, db.Schemas);
                    db.Schemas[schema.Name].Tables[tableName].Columns[columnName].SequenceName = sequenceName;
                }

                if (lineSegments[1].Equals("INDEX") || lineSegments[2].Equals("INDEX"))
                {
                    var clusterLine = totalLines[i + 2];
                    var isClustered = false;
                    if (!String.IsNullOrEmpty(clusterLine))
                    {
                        var clusterSegments = clusterLine.Split(Settings.SplitterFor.Space,
                                                                StringSplitOptions.RemoveEmptyEntries);
                        if (clusterSegments.Length > 3 && clusterSegments[3].Equals("CLUSTER"))
                        {
                            isClustered = true;
                            i = i + 3; // Set the processing to continue after cluster definition.
                        }
                    }

                    var schema = GetCurrentSchema(currentSearchPath, db.Schemas);

                    var indexAt = lineSegments[1].Equals("INDEX") ? 4 : 5;
                        // Different index number for unique and non-unique index.
                    var tableName = _text.RemoveDoubleQuotes(lineSegments[indexAt]);

                    if (!schema.Tables.ContainsKey(tableName))
                        throw new PgBirdieException(String.Format("Table {0} was not found. Is it included in config file under source -> schemas -> schema -> tables?", tableName));

                    var table = schema.Tables[tableName];

                    var index = _dbObject.GetIndex(line, lineSegments, isClustered);
                    index.ModelType = PgDbObjectType.Index;
                    index.Table = table;
                    index.OrderNo = orderNo;

                    // Validate index.
                    _validator.ValidateIndex(index);

                    table.Indexes.Add(index.Name, index);

                    orderNo++;
                    continue;
                }

                if (lineSegments[1].Equals("CONSTRAINT"))
                {
                    var previousLine = totalLines[i - 1].Trim();
                    var prevSegments = previousLine.Split(Settings.SplitterFor.Space,
                                                          StringSplitOptions.RemoveEmptyEntries);

                    var schema = GetCurrentSchema(currentSearchPath, db.Schemas);
                    var tableName = _text.RemoveDoubleQuotes(prevSegments[3]);

                    if (!schema.Tables.ContainsKey(tableName))
                        throw new Exception(
                            String.Format(
                                "Table {0} was not found. Is it included in config file under source -> schemas -> schema -> tables?",
                                tableName));

                    var table = schema.Tables[tableName];

                    var constraint = _dbObject.GetConstraint(lineSegments.ToList(), table);
                    constraint.ModelType = PgDbObjectType.Constraint;
                    constraint.OrderNo = orderNo;

                    switch (constraint.ConstraintType)
                    {
                        case ConstraintType.PrimaryKey:
                            table.PrimaryKey = constraint as PgPrimaryKey;
                            break;
                        case ConstraintType.UniqueKey:
                            table.UniqueKeys.Add(constraint.Name, constraint as PgUniqueKey);
                            break;
                        case ConstraintType.ForeignKey:
                            table.ForeignKeys.Add(constraint.Name, constraint as PgForeignKey);
                            break;
                    }

                    orderNo++;
                    continue;
                }

                // Get owners for schemas.
                // Line to be parsed:
                // -- Name: public; Type: ACL; Schema: -; Owner: postgres
                if (line.StartsWith("-- Name") && lineSegments[4].StartsWith("ACL") && lineSegments[6].StartsWith("-"))
                {
                    var schemaName = _text.RemoveCommaOrSemiColonFromTheEnd(lineSegments[2]);
                    var schema = db.Schemas[schemaName];
                    schema.Owner = _dbObject.GetOwner(line, schema);
                    continue;
                }

                if (line.StartsWith("REVOKE") || line.StartsWith("GRANT"))
                {
                    var dbObjectType = _dbObject.GetDbObjectTypeFromPrivilege(line);

                    PgPrivilege privilege;

                    switch (dbObjectType)
                    {
                        case PgDbObjectType.Database:
                            privilege = _dbObject.GetPrivilege(line, db);
                            db.Privileges.Add(privilege.Id, privilege);
                            break;
                        case PgDbObjectType.Schema:
                            {
                                var schemaSegments = line.Split(' ');
                                var schemaName = schemaSegments[schemaSegments.Length - 3];
                                var schema = GetCurrentSchema(schemaName, db.Schemas);
                                privilege = _dbObject.GetPrivilege(line, schema);
                                schema.Privileges.Add(privilege.Id, privilege);
                            }
                            break;
                        case PgDbObjectType.Table:
                            {
                                var schema = GetCurrentSchema(currentSearchPath, db.Schemas);
                                var dbObjectName = _dbObject.GetDbObjectNameFromPrivilege(line);
                                var table = schema.Tables[dbObjectName];
                                privilege = _dbObject.GetPrivilege(line, table);
                                table.Privileges.Add(privilege.Id, privilege);
                            }
                            break;
                        case PgDbObjectType.Sequence:
                            {
                                var schema = GetCurrentSchema(currentSearchPath, db.Schemas);
                                var dbObjectName = _dbObject.GetDbObjectNameFromPrivilege(line);
                                var sequence = schema.Sequences[dbObjectName];
                                privilege = _dbObject.GetPrivilege(line, sequence);
                                sequence.Privileges.Add(privilege.Id, privilege);
                            }
                            break;
                        default:
                            throw new Exception(String.Format("Could not handle db object type: {0}.", dbObjectType));
                    }

                    privilege.OrderNo = orderNo;

                    orderNo++;
                }
            }

            return db;
        }

        public void PopulateStaticDataFromPgDump(ref PgDatabase db, string text)
        {
            var allLines = text.Split(Settings.SplitterFor.WindowsLineBreak, StringSplitOptions.None);

            var currentSchemaName = String.Empty;
            var currentTableName = String.Empty;

            var totalCount = allLines.Length;
            for (var i = 0; i < totalCount; i++)
            {
                var line = allLines[i].Trim();

                // Skip irrelevant lines.
                if (String.IsNullOrEmpty(line) || line.Length < 13)
                    continue;

                var lineSegments = _text.RemoveCommaOrSemiColonFromTheEnd(line)
                    .Split(Settings.SplitterFor.Space, StringSplitOptions.RemoveEmptyEntries);

                if (lineSegments[1].Equals("search_path"))
                {
                    currentSchemaName = GetCurrentSearchPath(lineSegments);
                    continue;
                }

                if (lineSegments[0].Equals("--") && lineSegments[1].Equals("Data"))
                {
                    currentTableName = lineSegments[4].Remove(lineSegments[4].Length - 1);
                    continue;
                }

                if (line.StartsWith("INSERT"))
                {
                    string staticData;
                    var dataColumns = new Dictionary<string, PgDataColumn>();

                    var table = db.Schemas[currentSchemaName].Tables[currentTableName];

                    var startIndex = currentTableName.Length + 21; // Drops "INSERT... (" from the start.

                    if (line.EndsWith(";")) // Single line INSERT.
                    {
                        var length = line.Length - (startIndex + 2); // Drops ");" from the end.
                        staticData = line.Substring(startIndex, length);
                    }
                    else // Multiline INSERT.
                    {
                        var lines = new List<string>()
                            {
                                // Drops "INSERT... (" from the start.
                                line.Substring(startIndex, line.Length - startIndex)
                            };

                        var subIndex = i + 1;
                        for (var i2 = subIndex; i < totalCount; i2++)
                        {
                            var dataLine = allLines[i2];
                            var nextLine = String.Empty;
                            if (allLines.Length < i2 + 1)
                                nextLine = allLines[i2 + 1];

                            // Marks end of multiline static data definition.
                            if (dataLine.EndsWith(");") && (nextLine.StartsWith("INSERT") || String.IsNullOrEmpty(nextLine)))
                            {
                                dataLine = dataLine.Remove(dataLine.Length - 2, 2); // Drops ");" from the end.
                                lines.Add(dataLine);
                                i = i2;
                                break;
                            }

                            lines.Add(dataLine);
                        }

                        staticData = String.Join(Environment.NewLine, lines.ToArray());
                    }

                    string[] values = Regex.Split(staticData, Settings.SplitterFor.StaticData);

                    // If this pops up, something's wrong with the Regex.Split; # of columns/values should match.
                    if (table.Columns.Count != values.Length)
                        throw new Exception("Could not handle static data INSERT statement.");

                    var counter = 0;
                    foreach (var column in table.Columns.Values)
                    {
                        var dataColumn = new PgDataColumn(column, values[counter]);
                        dataColumns.Add(dataColumn.Name, dataColumn);

                        counter++;
                    }

                    var dataRow = new PgDataRow(table, dataColumns);
                    db.Schemas[currentSchemaName].Tables[currentTableName].DataRows.Add(dataRow.Uid, dataRow);
                }
            }
        }

        private string GetCurrentSearchPath(string[] segments)
        {
            return segments[3].Remove(segments[3].Length - 1, 1);
        }

        private PgSchema GetCurrentSchema(string schemaName, IDictionary<string, PgSchema> schemas)
        {
            if (!schemas.ContainsKey(schemaName))
                throw new Exception(String.Format("Schemas did not contain schema name {0} although it should.", schemaName));

            return schemas[schemaName];
        } 
    }
}